package com.mhkj.base.region.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;

import com.mhkj.base.region.entity.TSysNation;
import com.mics.core.system.base.repository.BaseRepository;

/**
 * @Author: Administrator
 * @Desc:
 * @Date: 15:58 2018/6/19
 */

public interface NationRepository extends BaseRepository<TSysNation> {
    /**
     * 根据Pid查找所有
     *
     * @return List<TSysNation>
     */
    List<TSysNation> findByParentId(Long pid);


    /**
     * 查询所有Pid下面的有项目的城市集合
     *
     * @param pid 省级id
     * @return List<TSysNation>
     */
    @Query(value = "SELECT DISTINCT  t.* FROM t_sys_nation t INNER JOIN t_item it ON t.id=it.city_id AND t.parent_id=?1", nativeQuery = true)
    List<TSysNation> findAllItemNation(Long pid);

    /**
     * 获取当前所有有项目的省级列表
     *
     * @return List<TSysNation>
     */
    @Query(value = "SELECT DISTINCT * FROM t_sys_nation tp WHERE tp.id IN (SELECT DISTINCT t.parent_id FROM t_sys_nation t INNER JOIN t_item it ON t.id=it.city_id)", nativeQuery = true)
    List<TSysNation> findAllItemProcince();




}
